* generate new variables from the Barcelona catastro
********************************************************************************
* prelims
********************************************************************************
{
clear all
set more off
cap log close
}
********************************************************************************
* catastro census tract characteristics over time
********************************************************************************
{

* load data
#d;
	local varscatkeep PlotCode PropOrder PropOrder2 Escalera Floor Door UCType2 
	MeanQualConst YearConst PropYear PropSqm House Apartment UCTyp ZIPCode
	Sqm* MunCode*
	;
#d cr

use "$data/int/08_900.dta", clear

keep `varscatkeep'

* add barri and census tract info
g year= 2016
merge m:1 PlotCode year using "$data/int/catastro_census_neigh_08_900.dta", keepusing(CPRO CMUN CDIS CSEC AreaSqm BARRI)
drop if _merge==2

* corrections
replace CPRO = "08" if _merge==1
replace CMUN = "019" if _merge==1
replace CDIS = "04" if PlotCode == "4927707DF2842F" & CDIS == ""
replace CSEC = "041" if PlotCode == "4927707DF2842F" & CSEC == ""
replace BARRI = "21" if PlotCode == "4927707DF2842F" & BARRI == ""
replace CDIS = "05" if PlotCode == "5247523DF2854G" & CDIS == ""
replace CSEC = "002" if PlotCode == "5247523DF2854G" & CSEC == ""
replace BARRI = "22" if PlotCode == "5247523DF2854G" & BARRI == ""
replace ZIPCode = "08017" if PlotCode == "5247523DF2854G" & ZIPCode == "00000"
replace CDIS = "06" if PlotCode == "0246504DF3804E" & CDIS == ""
replace CSEC = "079" if PlotCode == "0246504DF3804E" & CSEC == ""
replace BARRI = "32" if PlotCode == "0246504DF3804E"
replace CMUN = "019" if CMUN != "019"

drop _merge year

save "$data/temp/temp_catastro_csec.dta", replace

* generate variables
#d;
	local sqmvars SqmA SqmAES SqmB SqmC SqmE SqmGC SqmGH SqmGR SqmI SqmK SqmM 
	SqmO SqmP SqmR SqmT SqmV SqmYH SqmY SqmZ
	;
#d cr
	
forval year = 2019(-1)2009 {

use "$data/temp/temp_catastro_csec.dta", clear

	* constructions in reference year
	keep if (YearConst <= `year')

	* property characteristics
g SqmCSEC = 0
la var SqmCSEC "Total Sqm in CSEC"

foreach var in `sqmvars' {

	bys CPRO CMUN CDIS CSEC: egen `var'CSEC = total(`var')
	la var `var'CSEC "Total `var' in CSEC"
	replace SqmCSEC = SqmCSEC + `var'CSEC

}

foreach var in `sqmvars' {
	g Shr`var'CSEC = `var'CSEC / SqmCSEC
	la var Shr`var'CSEC "Shr of `var' in CSEC"

}

g QualConst = round(MeanQualConst, 1)
la var QualConst "Quality of Construction"

g UCT = substr(UCType2,1,4)
destring UCT, force replace
la var UCT "UC Type"

drop UCType2 UCTyp MeanQualConst

drop if PropSqm <= 10

* number of floors in Plot
egen floortag = tag(PlotCode Floor)
bys PlotCode: egen NFloors = total(floortag)
replace NFloors = 4 if PlotCode == "7513209DF2871D"
replace NFloors = 1 if NFloors == 0
la var NFloors "Number of Floors"
drop floortag

* number of properties in CSEC
g x = 1
bys CPRO CMUN CDIS CSEC: egen NProps = sum(x)
drop x
la var NProps "Number of Properties in the CSEC"

* number of apartments in plot
g x = (House==1|Apartment==1)
bys CPRO CMUN CDIS CSEC: egen NAptsCSEC = total(x)
la var NAptsCSEC "Number of Apartments in the CSEC"

* number of plots
egen tag = tag(PlotCode)
bys CPRO CMUN CDIS CSEC: egen NBlngs = total(tag)
drop tag

* only apartments
egen tag = tag(PlotCode) if x == 1

* year of construction and property year
foreach var in YearConst PropYear {

bys CPRO CMUN CDIS CSEC: egen t_Mean`var'CSEC = mean(`var') if tag == 1
bys CPRO CMUN CDIS CSEC: egen t_Med`var'CSEC = median(`var') if tag == 1
bys CPRO CMUN CDIS CSEC: egen t_Max`var'CSEC = max(`var') if tag == 1
bys CPRO CMUN CDIS CSEC: egen t_Min`var'CSEC = min(`var') if tag == 1
bys CPRO CMUN CDIS CSEC: egen Mean`var'CSEC = mean(t_Mean`var'CSEC) 
bys CPRO CMUN CDIS CSEC: egen Med`var'CSEC = mean(t_Med`var'CSEC) 
bys CPRO CMUN CDIS CSEC: egen Min`var'CSEC = mean(t_Min`var'CSEC) 
bys CPRO CMUN CDIS CSEC: egen Max`var'CSEC = mean(t_Max`var'CSEC)

* drop temp vars
drop t_Max`var'CSEC t_Min`var'CSEC t_Med`var'CSEC t_Mean`var'CSEC

}

la var MeanYearConstCSEC "Mean Year of Construction in CSEC"
la var MedYearConstCSEC "Median Year of Construction in CSEC"  
la var MinYearConstCSEC "Min Year of Construction in CSEC" 
la var MaxYearConstCSEC "Max Year of Construction in CSEC" 
la var MeanPropYearCSEC "Mean PropYear in CSEC"
la var MedPropYearCSEC "Median PropYear in CSEC"        
la var MaxPropYearCSEC "Max PropYear in CSEC"
la var MinPropYearCSEC "Min PropYear in CSEC" 
la var MeanYearConstCSEC "Mean Year of Construction in CSEC"
la var MedYearConstCSEC "Med Year of Construction in CSEC"
la var NBlngs "Number of Buildings in CSEC"

drop tag

* mean number of units per building
bys PlotCode: egen t_NApts = total(x) 
bys CPRO CMUN CDIS CSEC: egen MeanNAptsCSEC = mean(t_NApts) 
bys CPRO CMUN CDIS CSEC: egen MedNAptsCSEC = median(t_NApts) 
bys CPRO CMUN CDIS CSEC: egen MinNAptsCSEC = min(t_NApts) 
bys CPRO CMUN CDIS CSEC: egen MaxNAptsCSEC = max(t_NApts) 

la var MeanNAptsCSEC "Mean N Apts per blng in CSEC"
la var MedNAptsCSEC "Median N Apts per blng in CSEC"
la var MinNAptsCSEC "Median N Apts per blng in CSEC"
la var MaxNAptsCSEC "Median N Apts per blng in CSEC"

drop t_NApts

* mean number of floors
bys CPRO CMUN CDIS CSEC: egen t_MaxNFloors = max(NFloors) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MinNFloors = min(NFloors) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MedNFloors = median(NFloors) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MeanNFloors = mean(NFloors) if x==1
bys CPRO CMUN CDIS CSEC: egen MaxNFloorsCSEC = mean(t_MaxNFloors) 
bys CPRO CMUN CDIS CSEC: egen MinNFloorsCSEC = mean(t_MinNFloors) 
bys CPRO CMUN CDIS CSEC: egen MedNFloorsCSEC = mean(t_MedNFloors) 
bys CPRO CMUN CDIS CSEC: egen MeanNFloorsCSEC = mean(t_MeanNFloors) 

* min and max apartment size
bys CPRO CMUN CDIS CSEC: egen t_MaxAptSize = max(PropSqm) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MinAptSize = min(PropSqm) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MedAptSize = median(PropSqm) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MeanAptSize = mean(PropSqm) if x==1
bys CPRO CMUN CDIS CSEC: egen MaxAptSizeCSEC = mean(t_MaxAptSize) 
bys CPRO CMUN CDIS CSEC: egen MinAptSizeCSEC = mean(t_MinAptSize) 
bys CPRO CMUN CDIS CSEC: egen MedAptSizeCSEC = mean(t_MedAptSize) 
bys CPRO CMUN CDIS CSEC: egen MeanAptSizeCSEC = mean(t_MeanAptSize) 

* quality
bys CPRO CMUN CDIS CSEC: egen t_MaxQual = max(QualConst) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MinQual = min(QualConst) if x==1
bys CPRO CMUN CDIS CSEC: egen t_MedQual = median(QualConst) if x==1
bys CPRO CMUN CDIS CSEC: egen MaxQualCSEC = mean(t_MaxQual) 
bys CPRO CMUN CDIS CSEC: egen MinQualCSEC = mean(t_MinQual) 
bys CPRO CMUN CDIS CSEC: egen MedQualCSEC = mean(t_MedQual) 

la var NAptsCSEC "Number of Apts in CSEC"
la var MaxAptSizeCSEC "Max Apt Size in CSEC"
la var MinAptSizeCSEC "Min Apt Size in CSEC"
la var MedAptSizeCSEC "Med Apt Size in CSEC"
la var MaxQualCSEC "Max Apt Qual in CSEC"
la var MinQualCSEC "Min Apt Qual in CSEC"
la var MedQualCSEC "Med Apt Qual in CSEC"

drop t_MaxAptSize t_MinAptSize t_MedAptSize t_MeanAptSize t_MaxQual t_MedQual t_MinQual x
drop t_MeanNFloors t_MedNFloors t_MinNFloors t_MaxNFloors

* aggregate at the plot level
foreach var in PropSqm {

	bys CPRO CMUN CDIS CSEC: egen `var'CSEC_mean = mean(`var')
	bys CPRO CMUN CDIS CSEC: egen `var'CSEC_med = median(`var')
	bys CPRO CMUN CDIS CSEC: egen `var'CSEC_sd = sd(`var')
	
	drop `var'
	
	replace `var'CSEC_sd = 0 if `var'CSEC_sd == .

}

* drop variables defined at lower level of aggregation
#d
	drop PropOrder* Escalera Floor Door House Apartment UCT
	SqmA SqmAES SqmB SqmC SqmE SqmGC SqmGH SqmGR SqmI SqmK SqmM SqmO SqmP SqmR 
	SqmT SqmV SqmYH SqmY SqmZ SqmCommon PlotCode YearConst PropYear
	;
#d cr

* aggregate at plot level
duplicates drop CPRO CMUN CDIS CSEC, force

* year
g Year = `year'

* store
compress
save "$data/temp/catastro_csec_chars_`year'.dta", replace

}

* append
clear

forval year = 2009(1)2019 {

	append using "$data/temp/catastro_csec_chars_`year'.dta"

}

drop if CDIS==""

* store
order CSEC CDIS CMUN CPRO BARRI ZIPCode
sort CPRO CMUN CDIS CSEC Year
compress
save "$data/int/catastro_csec_chars.dta", replace

* erase temp file
cap erase "$data/temp/temp_catastro_csec.dta"

}
********************************************************************************
* property and plot characteristics
********************************************************************************
{
* load
#d;
	local varscatkeep PlotCode PropOrder PropOrder2 Escalera Floor Door UCType2 
	MeanQualConst YearConst PropYear PropSqm House Apartment UCTyp ZIPCode
	Sqm* MunCode*
	;
#d cr

use "$data/int/08_900.dta", clear

keep `varscatkeep'

* square meter variables
#d;
	local sqmvars SqmA SqmAES SqmB SqmC SqmE SqmGC SqmGH SqmGR SqmI SqmK SqmM 
	SqmO SqmP SqmR SqmT SqmV SqmYH SqmY SqmZ SqmCommon
	;
#d cr


* property characteristics
g SqmPLOT = 0
la var SqmPLOT "Total Sqm in PLOT"

foreach var in `sqmvars' {

	* share in property
	g Shr`var'PROP = `var' / PropSqm
	la var Shr`var'PROP "Shr of `var' in Property"
	* sum in plot
	bys PlotCode: egen `var'PLOT = total(`var')
	la var `var'PLOT "Total `var' in Plot"
	* total number of sqm in plot
	replace SqmPLOT = SqmPLOT+ `var'PLOT

}

foreach var in `sqmvars' {
	g Shr`var'PLOT = `var'PLOT / SqmPLOT
	la var Shr`var'PLOT "Shr of `var' in Plot"

}

drop ShrSqmCommonPROP

* add barri and census tract info
g year= 2016
merge m:1 PlotCode year using "$data/int/catastro_census_neigh_08_900.dta", keepusing(CPRO CMUN CDIS CSEC AreaSqm BARRI)
drop if _merge==2

* corrections
replace CPRO = "08" if _merge==1
replace CMUN = "019" if _merge==1
replace CDIS = "04" if PlotCode == "4927707DF2842F" & CDIS == ""
replace CSEC = "041" if PlotCode == "4927707DF2842F" & CSEC == ""
replace BARRI = "21" if PlotCode == "4927707DF2842F" & BARRI == ""
replace CDIS = "05" if PlotCode == "5247523DF2854G" & CDIS == ""
replace CSEC = "002" if PlotCode == "5247523DF2854G" & CSEC == ""
replace BARRI = "22" if PlotCode == "5247523DF2854G" & BARRI == ""
replace ZIPCode = "08017" if PlotCode == "5247523DF2854G" & ZIPCode == "00000"
replace CDIS = "06" if PlotCode == "0246504DF3804E" & CDIS == ""
replace CSEC = "079" if PlotCode == "0246504DF3804E" & CSEC == ""
replace BARRI = "32" if PlotCode == "0246504DF3804E"
replace CMUN = "019" if CMUN != "019"

drop _merge year

g QualConst = round(MeanQualConst, 1)
la var QualConst "Quality of Construction"

g UCT = substr(UCType2,1,4)
destring UCT, force replace
la var UCT "UC Type"

drop UCType2 UCTyp MeanQualConst

drop if PropSqm <= 10

* number of floors
egen floortag = tag(PlotCode Floor)
bys PlotCode: egen NFloors = total(floortag)
replace NFloors = 4 if PlotCode == "7513209DF2871D"
replace NFloors = 1 if NFloors == 0
la var NFloors "Number of Floors"
drop floortag

* number of properties
g x = 1
bys PlotCode: egen NProps = sum(x)
drop x
la var NProps "Number of Properties in the Plot"

* number of units
g x = (House==1|Apartment==1)
bys PlotCode: egen NApts = sum(x)
la var NApts "Number of Apartments in the Plot"

* min and max apartment size
bys PlotCode: egen t_MaxAptSize = max(PropSqm) if x==1
bys PlotCode: egen t_MinAptSize = min(PropSqm) if x==1
bys PlotCode: egen t_MedAptSize = median(PropSqm) if x==1
bys PlotCode: egen MaxAptSize = mean(t_MaxAptSize) 
bys PlotCode: egen MinAptSize = mean(t_MinAptSize) 
bys PlotCode: egen MedAptSize = mean(t_MedAptSize) 

bys PlotCode: egen t_MaxQual = max(QualConst) if x==1
bys PlotCode: egen t_MinQual = min(QualConst) if x==1
bys PlotCode: egen t_MedQual = median(QualConst) if x==1
bys PlotCode: egen MaxQual = mean(t_MaxQual) 
bys PlotCode: egen MinQual = mean(t_MinQual) 
bys PlotCode: egen MedQual = mean(t_MedQual) 

la var MaxAptSize "Max Apt Size in PLOT"
la var MinAptSize "Min Apt Size in PLOT"
la var MedAptSize "Med Apt Size in PLOT"
la var MaxQual "Max Apt Qual in PLOT"
la var MinQual "Min Apt Qual in PLOT"
la var MedQual "Med Apt Qual in PLOT"

drop t_MaxAptSize t_MinAptSize t_MedAptSize t_MaxQual t_MedQual t_MinQual x

* store
compress
save "$data/int/catastro_prop_chars.dta", replace

* aggregate at the plot level
foreach var in PropSqm {

	bys PlotCode: egen `var'PLOT_mean = mean(`var')
	bys PlotCode: egen `var'PLOT_med = median(`var')
	bys PlotCode: egen `var'PLOT_sd = sd(`var')
	
	drop `var'
	
	replace `var'PLOT_sd = 0 if `var'PLOT_sd == .

}

* drop variables defined at lower level of aggregation
#d
	drop PropOrder* Escalera Floor Door House Apartment UCT
	SqmA SqmAES SqmB SqmC SqmE SqmGC SqmGH SqmGR SqmI SqmK SqmM SqmO SqmP SqmR 
	SqmT SqmV SqmYH SqmY SqmZ SqmCommon *PROP
	;
#d cr

* aggregate at plot level
duplicates drop PlotCode, force

* adjustment: replace missing quality when qualconst available
foreach var in MaxQual MinQual MedQual {

	replace `var' = QualConst if `var ' == . & QualConst != .

}

* note: max qual is actually min (lower numbers are better)
rename MaxQual temp
rename MinQual MaxQual
rename temp MinQual
la var MaxQual "Max Apt Qual in PLOT"
la var MinQual "Min Apt Qual in PLOT"

* store
order PlotCode CSEC CDIS CMUN CPRO BARRI ZIPCode
compress
save "$data/int/catastro_plot_chars.dta", replace

}
********************************************************************************
* panel of new buildings in each census tract
********************************************************************************
{
use "$data/int/08_900.dta", replace

* keep variables of interest
keep PlotCode PropOrder PropOrder2 ProvCode MunCodeINE ZIPCode YearConst PropYear PropSqm House Apartment PropType2 Ref* LastYear* MeanQualConst

* add barri and census tract
g year= 2016
merge m:1 PlotCode year using "$data/int/catastro_census_neigh_08_900.dta", keepusing(CPRO CMUN CDIS CSEC BARRI AreaSqm)
keep if _merge==3
drop _merge year

* corrections
replace CMUN = "019" if CMUN != "019"

egen tract_tag = tag(CPRO CMUN CDIS CSEC)

* store temp data
save "$data/temp/temp_newbldngs.dta", replace

* now aggregate
forval year = 2019(-1)1999 {

	use "$data/temp/temp_newbldngs.dta", clear

	* keep only new or reformed buildings
	keep if (PropYear==`year' | YearConst==`year') | tract_tag == 1

	** all buildings
		* properties
	g x = 1 if YearConst==`year'
		* buildings
	egen tag = tag(PlotCode)
	replace tag = . if YearConst != `year'
		* properties with reforms
	g y = 1 if (PropYear==`year' & YearConst!=`year')
		* buildings with reforms
	egen tag2 = tag(PlotCode) if y == 1

	* number of properties
	bys CPRO CMUN CDIS CSEC: egen NewProps`year' = total(x)
	la var NewProps`year' "New Properties in `year' in CSEC"

	* number of buildings
	bys CPRO CMUN CDIS CSEC: egen NewBldings`year' = total(tag)
	la var NewBldings`year' "New Buildings in `year' in CSEC"
	
	* properties with total reforms
	bys CPRO CMUN CDIS CSEC: egen RefProps`year' = total(y)
	la var RefProps`year' "Properties with Reforms in `year' in CSEC"
	
	* buildings with total reforms
	bys CPRO CMUN CDIS CSEC: egen RefBldings`year' = total(tag2)
	la var RefBldings`year' "Buildings with Reforms in `year' in CSEC"
	
	* drop temp vars
	drop x tag y tag2
	
	** houses/apartments
		* properties
	g x = 1 if YearConst==`year' & (House==1|Apartment==1)
		* buildings
	egen tag = tag(PlotCode)
	replace tag = . if (YearConst != `year' | (House!=1 & Apartment!=1))
		* properties with reforms
	g y = 1 if PropYear==`year' & YearConst!=`year' & (House==1|Apartment==1)
		* buildings with reforms
	egen tag2 = tag(PlotCode) if y == 1

	* number of properties
	bys CPRO CMUN CDIS CSEC: egen NewApts`year' = total(x)
	la var NewApts`year' "New Apartments in `year' in CSEC"

	* number of buildings
	bys CPRO CMUN CDIS CSEC: egen NewAptBldings`year' = total(tag)
	la var NewAptBldings`year' "New Apartment Buildings in `year' in CSEC"
	
	* properties with total reforms
	bys CPRO CMUN CDIS CSEC: egen RefApts`year' = total(y)
	la var RefApts`year' "Apartments with Reforms in `year' in CSEC"
	
	* buildings with total reforms
	bys CPRO CMUN CDIS CSEC: egen RefAptBldings`year' = total(tag2)
	la var RefAptBldings`year' "Apartment Buildings with Reforms in `year' in CSEC"
	
	* drop temp vars
	drop x tag y tag2
	
	** size
	g x = PropSqm if YearConst==`year' & (House==1|Apartment==1)
	
	* new apartments
	bys CPRO CMUN CDIS CSEC: egen NewAptBldMeanSqm`year' = mean(x)
	la var NewAptBldMeanSqm`year' "New Apartment Buildings Mean Size in `year' in CSEC"
	bys CPRO CMUN CDIS CSEC: egen NewAptBldMedSqm`year' = median(x)
	la var NewAptBldMedSqm`year' "New Apartment Buildings Median Size in `year' in CSEC"
	
	drop x
	
	** quality
	g x = MeanQualConst if YearConst==`year' & (House==1|Apartment==1)
	
	* new apartments
	bys CPRO CMUN CDIS CSEC: egen NewAptBldMeanQual`year' = mean(x)
	la var NewAptBldMeanQual`year' "New Apartment Buildings Mean Quality in `year' in CSEC"
	
	bys CPRO CMUN CDIS CSEC: egen NewAptBldMedQual`year' = median(x)
	la var NewAptBldMedQual`year' "New Apartment Buildings Median Quality in `year' in CSEC"
	
	drop x
	
	* size reformed apts
	g x = PropSqm if (PropYear==`year' & YearConst!=`year') & (House==1|Apartment==1)
	
	* new apartments
	bys CPRO CMUN CDIS CSEC: egen NewAptRefMeanSqm`year' = mean(x)
	la var NewAptRefMeanSqm`year' "Reformed Apartment Buildings Mean Size in `year' in CSEC"
	
	bys CPRO CMUN CDIS CSEC: egen NewAptRefMedSqm`year' = median(x)
	la var NewAptRefMedSqm`year' "Reformed Apartment Buildings Median Size in `year' in CSEC"
	
	drop x
	
	* aggregate
	duplicates drop CPRO CMUN CDIS CSEC, force

	* drop variables not defined at the census tract level
	drop PlotCode-ReformType
	drop tract_tag
	
	* store
	compress
	save "$data/temp/08_900_blng_expan_`year'.dta", replace

}

* merge
clear

use "$data/temp/08_900_blng_expan_2019.dta", clear

forval year = 2018(-1)1999 {

	merge 1:1 CSEC CDIS CMUN CPRO using "$data/temp/08_900_blng_expan_`year'.dta"
	assert _merge==3
	drop _merge

}

* reshape
#d;
	local varsreshape NewProps NewBldings RefProps RefBldings NewApts 
	NewAptBldings RefApts RefAptBldings NewAptBldMeanSqm NewAptBldMedSqm 
	NewAptBldMeanQual NewAptBldMedQual NewAptRefMeanSqm NewAptRefMedSqm
	;
#d cr
reshape long `varsreshape', i(CSEC CDIS CMUN CPRO AreaSqm) j(Year)

* store
compress
save "$data/int/catastro_blng_expan.dta", replace

* erase temp files
forval year = 2019(-1)1999 {
	cap erase "$data/temp/08_900_blng_expan_`year'.dta"
}
cap erase "$data/temp/temp_newbldngs.dta"

}
********************************************************************************
* closing
********************************************************************************
{
cap log close
clear all
}
